<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ page import="com.c0nflict.server.*, com.mysql.jdbc.Statement, com.mysql.jdbc.ResultSet, java.util.HashMap, java.text.DecimalFormat" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%! 
private class TableRating {
	public int tableId = 0;
	public int votes = 0;
	public int ratingSummary = 0;
}
%>
<%@ include file="admin_header.jspf" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>c0nflict: Database Tables</title>
</head>
<body>
<%
Database db = tm.db;

DecimalFormat ratingFormatter = new DecimalFormat("0.00;-");

if (db == null)
	out.println("Unable to connect to database!");
else {
	Statement stmt = (Statement)db.getConnection().createStatement();
	
	HashMap<Integer, TableRating> tableRatings = new HashMap<Integer, TableRating>();
	
	String query = "select table_ratings from dq_user where table_ratings is not null";
	ResultSet rs = (ResultSet)stmt.executeQuery(query);
	
	int tableId = 0;
	int rating = 0;
	int ratingCount = 0;
	double avgRating = 0.0;
	String str = null;
	TableRating tableRating = null;
	
	while (rs.next()) {
		str = rs.getString("table_ratings"); // Format: <tbl_id1>:<tbl_rating1>,<tbl_id2>:<tbl_rating2>,etc....
		String[] ratings = str.split(",");
		ratingCount = ratings.length;
		for (int i = 0; i < ratingCount; i++) {
			String[] values = ratings[i].split(":");
			tableId = new Integer(values[0]).intValue();
			rating = new Integer(values[1]).intValue();
			
			if (tableRatings.containsKey(tableId)) {
				tableRating = (TableRating)tableRatings.get(tableId);
				tableRating.votes++;
				tableRating.ratingSummary += rating;
			}
			else {
				tableRating = new TableRating();
				tableRating.tableId = tableId;
				tableRating.votes = 1;
				tableRating.ratingSummary = rating;
				tableRatings.put(tableId, tableRating);
			}
			
			tableRating = null;
		}
	}
	
	query = "select (select username from jforum_users where user_id = t.author) username, t.* from dq_tables t where approved = 1 order by table_id";
	rs = (ResultSet)stmt.executeQuery(query);
	
	rs.last();
	
	int recordCount = rs.getRow();
	
	rs.beforeFirst();
%>
<center><h3 style="color: #ccc;"><b><%= tm.WATCHDOG.toString() %></b></h3></center>

<center><font size="5" color="white" face="verdana"><b><a name="players">Database Tables</a></b></font></center>
<br/>
<center><font size="3" color="white" face="verdana"><b><a name="players">Approved Tables</a></b></font></center>

<div class="dbdataContainer">
<font size="2" color="white" face="verdana"><%= recordCount %> records found:</font>
<table class="sortable" cellpadding="4" cellspacing="0">
<thead>
<tr>
	<th>ID</th>
	<th>Name</th>
	<th>Author</th>
	<th>Width</th>
	<th>Height</th>
	<th>Attack<br/>Style</th>
	<th>Start<br/>Areas</th>
	<th>Start<br/>Dice</th>
	<th>Dice<br/>Cap</th>
	<th>Max<br/>Roll</th>
	<th>%<br/>Areas</th>
	<th>%<br/>Dice</th>
	<th>Rating</th>
</tr>
</thead>
<tfoot>
<tr><td colspan="0">Approved tables in database as of <%= tm.WATCHDOG.toString() %></td></tr>
</tfoot>
<tbody>
	<% while (rs.next()) {
		
		if (tableRatings.containsKey(tableId)) { 
			tableRating = tableRatings.get(tableId);
			avgRating = (double)tableRating.ratingSummary / (double)tableRating.votes;
		}
		else {
			tableRating = null;
			avgRating = -1.0;
		}
		
		tableId = rs.getInt("table_id"); %>
<tr <% if (tableRating != null && avgRating <= 2.00) { %>bgcolor="darkred"<% } 
		else if (tableRating != null && avgRating <= 3.00) { %>bgcolor="#cc6600"<% } 
		else if (rs.getRow() % 2 == 1) { %> bgcolor="#222" <% } %>>
	<td><%= tableId %></td>
	<td><%= rs.getString("tablename") %></td>
	<td><%= rs.getString("username") + " (" + rs.getInt("author") + ")" %></td>
	<td><%= rs.getInt("width") %></td>
	<td><%= rs.getInt("height") %></td>
	<td><%= (rs.getInt("partialvictory") == 0) ? "AON" : "Partial" %></td>
	<td><%= rs.getInt("starting_lands") %></td>
	<td><%= rs.getInt("starting_dice") %></td>
	<td><%= rs.getInt("max_dice_per") %></td>
	<td><%= rs.getInt("max_roll") %></td>
	<td><%= rs.getDouble("movable_land") %></td>
	<td><%= rs.getDouble("movable_dice") %></td>
	<% if (tableRating != null) { %>
	<td><%= ratingFormatter.format(avgRating) %> (<%= tableRating.votes %>)</td>
	<% } else { %>
	<td>-</td>
	<% } %>
</tr>
	<% } %>
</tbody>
</table>
</div>
<br/>
<%
	query = "select (select username from jforum_users where user_id = t.author) username, t.* from dq_tables t where approved = 0 order by table_id";
	rs = (ResultSet)stmt.executeQuery(query);
	
	rs.last();
	
	recordCount = rs.getRow();
	
	rs.beforeFirst();
%>
<center><font size="3" color="white" face="verdana"><b><a name="players">Un-approved Tables</a></b></font></center>

<div class="dbdataContainer">
<font size="2" color="white" face="verdana"><%= recordCount %> records found:</font>
<table class="sortable" cellpadding="4" cellspacing="0">
<thead>
<tr>
	<th>ID</th>
	<th>Name</th>
	<th>Author</th>
	<th>Width</th>
	<th>Height</th>
	<th>Attack<br/>Style</th>
	<th>Start<br/>Areas</th>
	<th>Start<br/>Dice</th>
	<th>Dice<br/>Cap</th>
	<th>Max<br/>Roll</th>
	<th>%<br/>Areas</th>
	<th>%<br/>Dice</th>
	<th>Rating</th>
</tr>
</thead>
<tfoot>
<tr><td colspan="0">Tables in database as of <%= tm.WATCHDOG.toString() %></td></tr>
</tfoot>
<tbody>
	<% while (rs.next()) {
		
		if (tableRatings.containsKey(tableId)) { 
			tableRating = tableRatings.get(tableId);
			avgRating = (double)tableRating.ratingSummary / (double)tableRating.votes;
		}
		else {
			tableRating = null;
			avgRating = -1.0;
		}
		
		tableId = rs.getInt("table_id"); %>
<tr <% if (tableRating != null && avgRating <= 2.00) { %>bgcolor="darkred"<% } 
		else if (tableRating != null && avgRating <= 3.00) { %>bgcolor="#cc6600"<% } 
		else if (rs.getRow() % 2 == 1) { %> bgcolor="#222" <% } %>>
	<td><%= tableId %></td>
	<td><%= rs.getString("tablename") %></td>
	<td><%= rs.getString("username") + " (" + rs.getInt("author") + ")" %></td>
	<td><%= rs.getInt("width") %></td>
	<td><%= rs.getInt("height") %></td>
	<td><%= (rs.getInt("partialvictory") == 0) ? "AON" : "Partial" %></td>
	<td><%= rs.getInt("starting_lands") %></td>
	<td><%= rs.getInt("starting_dice") %></td>
	<td><%= rs.getInt("max_dice_per") %></td>
	<td><%= rs.getInt("max_roll") %></td>
	<td><%= rs.getDouble("movable_land") %></td>
	<td><%= rs.getDouble("movable_dice") %></td>
	<% if (tableRating != null) { %>
	<td><%= ratingFormatter.format(avgRating) %> (<%= tableRating.votes %>)</td>
	<% } else { %>
	<td>-</td>
	<% } %>
</tr>
	<% } %>
</tbody>
</table>
</div>
<% } %>
<%@ include file="admin_footer.jspf" %>
</body>
</html>